package com.durablemethods.futures.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.logging.Logger;

import twitter4j.Twitter;
import twitter4j.TwitterException;
import twitter4j.TwitterFactory;
import twitter4j.conf.ConfigurationBuilder;

import com.durablemethods.futures.client.KillFilledMarketException;
import com.durablemethods.futures.client.MarketLevelNoLongerAvailableException;
import com.durablemethods.futures.client.NHLBidASk;
import com.durablemethods.futures.client.NHLMarket;
import com.durablemethods.futures.client.NHLMarketsService;
import com.durablemethods.futures.client.NHLTeam;
import com.durablemethods.futures.client.NHLTradeEntryDto;
import com.durablemethods.futures.client.NHLTrader;
import com.durablemethods.futures.client.OddLotException;
import com.google.appengine.api.rdbms.AppEngineDriver;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;

public class NHLMarketServiceImpl extends RemoteServiceServlet implements NHLMarketsService {
	private static final String CONNECTION_STRING = "jdbc:google:rdbms://durablemethods.com:duravest:durable-futures-db";
	private static final long serialVersionUID = 839966946043017687L;
	private Logger log = Logger.getLogger(NHLMarketServiceImpl.class.getName());
	private ConfigurationBuilder cb;
	private TwitterFactory tf;
	public NHLMarketServiceImpl() {
		cb = new ConfigurationBuilder();
		cb.setDebugEnabled(true)
		  .setOAuthConsumerKey("SCfsGYMK0SKDAKCv18YPEw")
		  .setOAuthConsumerSecret("rOcVwULZ0en0tezWVUD3odtEKAqfZmjsUJgu2NBkD0U")
		  .setOAuthAccessToken("1358017951-V3L4NcuHGpXDr8mp5FhUIcK5n6GgKggkCKoRBkM")
		  .setOAuthAccessTokenSecret("iDMAXrIZUCZllpz1NmJksOE6hrm2k9XiSd53Ru05HI");
		tf = new TwitterFactory(cb.build());
	}
	
	public List<NHLMarket> getMarkets(List<NHLTeam> teams) {
		ArrayList<NHLMarket> returnMarkets = new ArrayList<NHLMarket>();
		if (teams.isEmpty()){
			return returnMarkets;
		}
		long start = System.currentTimeMillis();
		Map<String, NHLMarket> marketMapForProcessing = new HashMap<String, NHLMarket>();
		Map<String, NHLTeam> teamsMap = new HashMap<String, NHLTeam>();
		for (NHLTeam team : teams) {
			NHLMarket market = new NHLMarket();
			market.setTeam(team);
			marketMapForProcessing.put(team.getId(), market);
			teamsMap.put(team.getId(), team);
		}
		Connection c = null;
		try {
			DriverManager.registerDriver(new AppEngineDriver());
			c = DriverManager.getConnection(CONNECTION_STRING,"root","");
			PreparedStatement ps = c.prepareStatement("select * from durable_futures.BID_ASK where status = 'O'");
			ResultSet rs = ps.executeQuery();
			while (rs.next()){
				NHLBidASk newBidAsk = new NHLBidASk();
				newBidAsk.setId(rs.getInt("ID"));
				newBidAsk.setTransactionDate(rs.getTimestamp("TRANSACTION_DATE"));
				newBidAsk.setTeam(teamsMap.get(rs.getString("TEAM_ID")));
				newBidAsk.setTraderId(rs.getString("TRADER_ID"));
				newBidAsk.setPrice(rs.getDouble("PRICE"));
				newBidAsk.setVolume(rs.getInt("QUANTITY"));
				NHLMarket nhlMarket = marketMapForProcessing.get(newBidAsk.getTeam().getId());
				nhlMarket.addBidAsk(newBidAsk);
			}
			ps.close();
			ps = c.prepareStatement("SELECT max(t1.TRANSACTION_DATE) as transaction_date, t1.TEAM_ID, t1.price FROM durable_futures.TRADES t1   JOIN (SELECT TEAM_ID, MAX(transaction_date) transaction_date FROM durable_futures.TRADES GROUP BY TEAM_ID) t2 ON t1.TEAM_ID = t2.TEAM_ID AND t1.transaction_date = t2.transaction_date  group by t1.TEAM_ID, t1.price ");
			ResultSet lasts = ps.executeQuery();
			while(lasts.next()){
				String teamId = lasts.getString("team_id");
				Double price = lasts.getDouble("price");
				if (marketMapForProcessing.containsKey(teamId)){
					NHLMarket nhlMarket = marketMapForProcessing.get(teamId);
					nhlMarket.setLastTrade(price);					
				}
			}
			c.close();
			long end = System.currentTimeMillis();
			log.info("Finished the market update in " + (end-start));
		}catch (SQLException e) {
			log.info(e.getMessage());
			e.printStackTrace();
		}
		returnMarkets = new ArrayList<NHLMarket>(marketMapForProcessing.values());
		
		return returnMarkets;
	}
	public boolean killBidAsk(Integer bidAskId) throws KillFilledMarketException{
		Connection c = null;
		
		try {
			DriverManager.registerDriver(new AppEngineDriver());
			c = DriverManager.getConnection(CONNECTION_STRING,"root","");
			PreparedStatement ps = c.prepareStatement("select status from  durable_futures.BID_ASK where id = ?");
			ps.setInt(1,bidAskId);
			ResultSet rs = ps.executeQuery();
			String status = "";
			while(rs.next()){
				status = rs.getString("status");
			}
			if (status.equalsIgnoreCase("O")){
				ps = c.prepareStatement("update durable_futures.BID_ASK set status = 'K' where id = ?");
				ps.setInt(1,bidAskId);
				ps.executeUpdate();
			}else{
				throw new KillFilledMarketException();
			}
		} catch (SQLException e) {
			log.info(e.getMessage());
			e.printStackTrace();
			
		}
		
		return true;
	}
	
	public NHLBidASk insertBidAsk(NHLBidASk bidAsk) throws OddLotException{
		Integer volume = bidAsk.getVolume();
		if (volume%5!=0){
			throw new OddLotException();
		}
		int units= Math.abs(volume/5);
		Integer bidAskId = generateID();
		bidAsk.setId(bidAskId);
		Timestamp tradeTimeStamp = new Timestamp(new Date().getTime());
		Connection c = null;
		int multiplier = 1;
		if (volume<0){
			multiplier  = -1;
		}
		try {
			DriverManager.registerDriver(new AppEngineDriver());
			c = DriverManager.getConnection(CONNECTION_STRING,"root","");
			PreparedStatement ps = c.prepareStatement("insert into durable_futures.BID_ASK ( ID,TRANSACTION_DATE,TEAM_ID,TRADER_ID,PRICE,QUANTITY,SEASON_ID,STATUS ) values (?,?,?,?,?,?,?,?)");
			for (int i = 0; i < units; i++) {
				ps.setInt(1,generateID());
				ps.setTimestamp(2, tradeTimeStamp);
				ps.setString(3, bidAsk.getTeam().getId());
				ps.setString(4, bidAsk.getTraderName());
				ps.setDouble(5, bidAsk.getPrice());
				ps.setInt(6, 5 * multiplier);
				ps.setInt(7,2013);
				ps.setString(8, "O");
				ps.executeUpdate();
			}
			ps.close();			
			c.close();
		} catch (SQLException e) {
			log.info(e.getMessage());
			e.printStackTrace();
		}
		return bidAsk;
		
	}

	public NHLTradeEntryDto saveTrade(NHLTradeEntryDto newTrade) {
		Connection c = null;
		Integer tradeId = generateID();
		newTrade.setId(tradeId);
		Integer bidAskId = newTrade.getBidAskId();
		Timestamp tradeTimeStamp = new Timestamp(new Date().getTime());
		if (bidAskId==null){
			bidAskId=-1;
		}
	      try {	    	 
			DriverManager.registerDriver(new AppEngineDriver());
			c = DriverManager.getConnection(CONNECTION_STRING,"root","");
			PreparedStatement ps = c.prepareStatement("insert into durable_futures.TRADES (ID, TRANSACTION_DATE,TEAM_ID,TRADER_ID,PRICE, QUANTITY,SEASON_ID,STATUS,BID_ASK_ID) VALUES (?,?,?,?,?,?,?,?,?) ");
			//buy
			ps.setInt(1,tradeId);
			ps.setTimestamp(2, tradeTimeStamp);
			ps.setString(3, newTrade.getTeamId());
			ps.setString(4, newTrade.getBuyer().getGoogleAccount());
			ps.setDouble(5, newTrade.getPrice());
			ps.setInt(6, newTrade.getShares());
			ps.setInt(7,2013);
			ps.setString(8, "A");
			ps.setInt(9, bidAskId);
			ps.executeUpdate();
			
			//sell
			ps.setInt(1,tradeId);
			ps.setTimestamp(2, tradeTimeStamp );
			ps.setString(3, newTrade.getTeamId());
			ps.setString(4, newTrade.getSeller().getGoogleAccount());
			ps.setDouble(5, newTrade.getPrice());
			ps.setInt(6, newTrade.getShares()*-1);
			ps.setInt(7,2013);
			ps.setString(8, "A");
			ps.setInt(9, bidAskId);
			ps.executeUpdate();
			publishTradeToTwitter(newTrade);
			publishEmails(newTrade);
			ps.close();
			c.close();
		} catch (SQLException e) {
			log.info(e.getMessage());
			e.printStackTrace();
			return null;
		}
	      return newTrade;
	}
	
	private void publishEmails(NHLTradeEntryDto newTrade) {
		// TODO Auto-generated method stub
		
	}

	private void publishTradeToTwitter(NHLTradeEntryDto newTrade) {
		

		Twitter twitter = tf.getInstance();
		String twitterUpdate = createTwitterString(newTrade);
		try {
			twitter.updateStatus(twitterUpdate);
			
		} catch (TwitterException e) {
			log.severe(e.getMessage());
		}
		
	}

	private String createTwitterString(NHLTradeEntryDto newTrade) {
		StringBuilder builder = new StringBuilder();
		String price = NumberFormat.getCurrencyInstance().format(newTrade.getPrice());
		String buyerString="";
		if(newTrade.getBuyer().getTwitterHandle()==null || newTrade.getBuyer().getTwitterHandle().isEmpty()){
			buyerString = newTrade.getBuyer().getAlias();
		}else{
			buyerString = newTrade.getBuyer().getTwitterHandle();
		}
		String sellerString="";
		if(newTrade.getSeller().getTwitterHandle()==null || newTrade.getSeller().getTwitterHandle().isEmpty()){
			sellerString = newTrade.getSeller().getAlias();
		}else{
			sellerString = newTrade.getSeller().getTwitterHandle();
		}
		builder.append("#TSMConfirm: ").append(buyerString).append(" buys / ").append(sellerString).append(" sells ");
		builder.append(newTrade.getShares()).append(" #TSM").append(newTrade.getTeamId()).append(" ").append(price).append("(").append(newTrade.getId()).append(")");
		return builder.toString();
	}

	private Integer generateID(){
		Random r = new Random();
		int Low = 1000000;
		int High = 2000000;
		int R = r.nextInt(High-Low) + Low;
		return R;
	}

	@Override
	public List<NHLTradeEntryDto> getAllTrades() {
		Map<Integer,NHLTradeEntryDto> processMap = new HashMap<Integer, NHLTradeEntryDto>();
		List<NHLTradeEntryDto> returnList = new  ArrayList<NHLTradeEntryDto>();
		Connection c = null;
		try {
			DriverManager.registerDriver(new AppEngineDriver());
			c = DriverManager.getConnection(CONNECTION_STRING,"root","");
			PreparedStatement ps = c.prepareStatement("select t.ID, t.TRANSACTION_DATE, t.TEAM_ID,t.QUANTITY, t.PRICE, tr.google_account,tr.alias from durable_futures.TRADES t, durable_futures.TRADERS tr where tr.google_account = t.trader_id and t.status not in ('I', 'C') ");
			ResultSet rs = ps.executeQuery();
			while (rs.next()){
				int tradeId = rs.getInt("ID");
				int quantity = rs.getInt("quantity");
				double price = rs.getDouble("price");
				NHLTrader trader = new NHLTrader();
				trader.setAlias(rs.getString("alias"));
				trader.setGoogleAccount(rs.getString("google_account"));
				if (processMap.containsKey(tradeId)){
					NHLTradeEntryDto nhlTradeEntryDto = processMap.get(tradeId);
					if (quantity<0){
						nhlTradeEntryDto.setSeller(trader);
					}else{
						nhlTradeEntryDto.setBuyer(trader);						
					}
				}else{
					NHLTradeEntryDto nhlTradeEntryDto = new NHLTradeEntryDto();
					nhlTradeEntryDto.setPrice(price);
					if (quantity<0){
						nhlTradeEntryDto.setSeller(trader);
						nhlTradeEntryDto.setShares(quantity*-1);
					}else{
						nhlTradeEntryDto.setBuyer(trader);
						nhlTradeEntryDto.setShares(quantity);
					}
					nhlTradeEntryDto.setId(tradeId);
					nhlTradeEntryDto.setTeamId(rs.getString("TEAM_ID"));
					nhlTradeEntryDto.setTransactionDate(rs.getTimestamp("TRANSACTION_DATE"));
					processMap.put(tradeId, nhlTradeEntryDto);
				}
			}
			ps.close();
			c.close();
		} catch (SQLException e) {
			log.severe(e.getMessage());
			e.printStackTrace();
		}
		returnList.addAll(processMap.values());
		Collections.sort(returnList, new TradeDTOSorter());
		return returnList;
	}
	
	
	class TradeDTOSorter implements Comparator<NHLTradeEntryDto>{

		@Override
		public int compare(NHLTradeEntryDto o1, NHLTradeEntryDto o2) {
			return o2.getTransactionDate().compareTo(o1.getTransactionDate());
		}
		
	}


	@Override
	public List<NHLBidASk> getMyMarkets(String goolgle_account) {
		 List<NHLBidASk> returnList = new ArrayList<NHLBidASk>();
		 Connection c = null;
			try {
				DriverManager.registerDriver(new AppEngineDriver());
				c = DriverManager.getConnection(CONNECTION_STRING,"root","");
				PreparedStatement ps = c.prepareStatement("select * from durable_futures.BID_ASK where trader_id = ? and status='O' order by team_id, price desc ");
				ps.setString(1, goolgle_account);
				ResultSet rs = ps.executeQuery();
				while (rs.next()){
					NHLBidASk newBidAsk = new NHLBidASk();
					newBidAsk.setId(rs.getInt("ID"));
					newBidAsk.setTransactionDate(rs.getTimestamp("TRANSACTION_DATE"));
					NHLTeam team = new NHLTeam();
					team.setId(rs.getString("TEAM_ID"));
					newBidAsk.setTeam(team);
					newBidAsk.setTraderId(rs.getString("TRADER_ID"));
					newBidAsk.setPrice(rs.getDouble("PRICE"));
					newBidAsk.setVolume(rs.getInt("QUANTITY"));
					returnList.add(newBidAsk);
				}
				ps.close();
				c.close();
			} catch (SQLException e) {
				log.severe(e.getMessage());
				e.printStackTrace();
			}
		return returnList;
	}

	@Override
	public NHLTradeEntryDto processHitLift(NHLTrader trader, NHLMarket market) throws MarketLevelNoLongerAvailableException {
		Connection c = null;
		try {
			DriverManager.registerDriver(new AppEngineDriver());
			c = DriverManager.getConnection(CONNECTION_STRING,"root","");
			PreparedStatement ps;
			NHLTradeEntryDto tradeToProcess = market.getTradeToProcess();
			Integer volume = market.getBidAskToProcess().getVolume();
			if (volume>0){
				ps = c.prepareStatement("select b.*, t.alias, t.twitter_handle from durable_futures.BID_ASK b,durable_futures.TRADERS t  where team_id=? and quantity>0 and status='O' and price = ? and b.TRADER_ID = t.google_account  order by TRANSACTION_DATE desc limit 1 ");
				tradeToProcess.setSeller(trader);
			}else{
				ps = c.prepareStatement("select b.*, t.alias, t.twitter_handle from durable_futures.BID_ASK b,durable_futures.TRADERS t  where team_id=? and quantity<0 and status='O' and price = ? and b.TRADER_ID = t.google_account  order by TRANSACTION_DATE desc limit 1 ");
				tradeToProcess.setBuyer(trader);
			}
			ps.setString(1, market.getTeam().getId());
			ps.setDouble(2, market.getBidAskToProcess().getPrice());
			ResultSet rs = ps.executeQuery();
			NHLBidASk newBidAsk = null;
			while (rs.next()){
				newBidAsk = new NHLBidASk();
				newBidAsk.setId(rs.getInt("ID"));
				newBidAsk.setTransactionDate(rs.getTimestamp("TRANSACTION_DATE"));
				String alias = rs.getString("alias");
				String twitterHandle = rs.getString("twitter_handle");
				if (volume>0){
					tradeToProcess.getBuyer().setAlias(alias);
					tradeToProcess.getBuyer().setTwitterHandle(twitterHandle);
				}else{
					tradeToProcess.getSeller().setAlias(alias);					
					tradeToProcess.getSeller().setTwitterHandle(twitterHandle);
				}
				NHLTeam team = new NHLTeam();
				team.setId(rs.getString("TEAM_ID"));
				newBidAsk.setTeam(team);
				newBidAsk.setTraderId(rs.getString("TRADER_ID"));
				newBidAsk.setPrice(rs.getDouble("PRICE"));
				newBidAsk.setVolume(rs.getInt("QUANTITY"));
			}
			if (newBidAsk != null){
				ps = c.prepareStatement("update durable_futures.BID_ASK set status = 'F' where id = ?");
				ps.setInt(1,newBidAsk.getId());
				ps.executeUpdate();	
				tradeToProcess.setBidAskId(newBidAsk.getId());
				saveTrade(tradeToProcess);
			}else{
				throw new MarketLevelNoLongerAvailableException();
			}
			ps.close();
			c.close();
		} catch (SQLException e) {
			log.info(e.getMessage());
			e.printStackTrace();
			
		}
		
		return market.getTradeToProcess();
	}

}
